﻿using NfsCommon;
using NfsCommon.DbHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NfsDAL
{
    /// <summary>
    /// 数据访问类:authorization
    /// </summary>
    public partial class AuthorizationDAL
    {
        public AuthorizationDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "t_authorization");
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_authorization");
            strSql.Append(" where id=" + id + " ");
            return DbHelperMySQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(NfsModel.authorization model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.agency_id != null)
            {
                strSql1.Append("agency_id,");
                strSql2.Append("" + model.agency_id + ",");
            }
            if (model.start_time != null)
            {
                strSql1.Append("start_time,");
                strSql2.Append("'" + model.start_time + "',");
            }
            if (model.end_time != null)
            {
                strSql1.Append("end_time,");
                strSql2.Append("'" + model.end_time + "',");
            }
            if (model.valid != null)
            {
                strSql1.Append("valid,");
                strSql2.Append("" + model.valid + ",");
            }
            if (model.time != null)
            {
                strSql1.Append("time,");
                strSql2.Append("'" + model.time + "',");
            }
            strSql.Append("insert into t_authorization(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(NfsModel.authorization model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_authorization set ");
            if (model.agency_id != null)
            {
                strSql.Append("agency_id=" + model.agency_id + ",");
            }
            else
            {
                strSql.Append("agency_id= null ,");
            }
            if (model.start_time != null)
            {
                strSql.Append("start_time='" + model.start_time + "',");
            }
            else
            {
                strSql.Append("start_time= null ,");
            }
            if (model.end_time != null)
            {
                strSql.Append("end_time='" + model.end_time + "',");
            }
            else
            {
                strSql.Append("end_time= null ,");
            }
            if (model.valid != null)
            {
                strSql.Append("valid=" + model.valid + ",");
            }
            else
            {
                strSql.Append("valid= null ,");
            }
            if (model.time != null)
            {
                strSql.Append("time='" + model.time + "',");
            }
            else
            {
                strSql.Append("time= null ,");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_authorization ");
            strSql.Append(" where id=" + id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_authorization ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.authorization GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" id,agency_id,start_time,end_time,valid,time ");
            strSql.Append(" from t_authorization ");
            strSql.Append(" where id=" + id + "");
            NfsModel.authorization model = new NfsModel.authorization();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.authorization DataRowToModel(DataRow row)
        {
            NfsModel.authorization model = new NfsModel.authorization();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["agency_id"] != null && row["agency_id"].ToString() != "")
                {
                    model.agency_id = int.Parse(row["agency_id"].ToString());
                }
                if (row["start_time"] != null && row["start_time"].ToString() != "")
                {
                    model.start_time = DateTime.Parse(row["start_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }
                if (row["time"] != null && row["time"].ToString() != "")
                {
                    model.time = DateTime.Parse(row["time"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,agency_id,start_time,end_time,valid,time ");
            strSql.Append(" FROM t_authorization ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_authorization ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperMySQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from t_authorization T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /*
        */

        #endregion  Method
        #region  自定义方法
        #region PC模糊查询
        public DataTable GetAuthorizationBycondtion(int pageindex, int pagesize,int id)
        {
            int startindex = (pageindex - 1) * pagesize;
            string strSql = string.Format(@" select top "+pagesize+" * from (select row_number() over(order by au.id desc ) as rownumber, au.*,ag.name as AgencyName from t_authorization as au left join t_agency as ag on au.agency_id=ag.id)as a where 1=1 and   valid=1 and agency_id=" +id+" ");
            strSql += " and a.rownumber>" + startindex + " ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        public object GetAuthorizationBycondtionCount(int id)
        {
            string strSql = string.Format(@"select count(*) from t_authorization where  valid=1  and agency_id=" + id + "");
           // strSql += " order by id desc ";
            return DbHelperMySQL.GetSingle(strSql);
        }
        #endregion
        #endregion  MethodEx
    }
}

